USE SAS
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetAccountContacts')
	BEGIN
		DROP  Procedure  dbo.GetAccountContacts
	END

GO

CREATE Procedure dbo.GetAccountContacts
	@accountGUID	VARCHAR(100)
,	@userId			VARCHAR(10) = '0'
,	@isActive		VARCHAR(10) = NULL
AS
BEGIN
	
	IF(@isActive IS NOT NULL)
		SET @ISACTIVE = CASE WHEN @ISACTIVE IN ('X','1') THEN '1' ELSE '0' END

	;WITH A AS(
		-- CONTACT ENTRIES IN TR_PARTNER
		SELECT
			p.PartnerNo				as [ContactNo]
		,	p.PartnerLastName		as [LastName]
		,	p.PartnerFirstName		as [FirstName]
		,	p.PartnerGUID			as [PKID]
		,	p.IsActive
		FROM dbo.TR_Partner as p with (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			on r.SourceGUID = p.PartnerGUID 
		WHERE	
			p.PartnerCategory = 1	-- PERSON
		AND p.PartnerType = 3		-- CONTACT
		AND	r.RelationshipType = 3	-- ACCOUNT
		AND r.Partner2 = ISNULL(@userID, 0)
		AND p.ISActive = ISNULL(@isActive, p.IsActive)
	), B AS (
		SELECT
			p.PartnerName			as [AccountName]
		,	A.PKID
		FROM TR_PARTNER As p WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			ON r.Partner2 = p.PartnerNo
		INNER JOIN A
			ON A.PKID = r.SourceGUID
		WHERE
			r.RelationshipType = 8
		AND	p.PartnerCategory = 2 -- ORGANIZATION
		AND p.PartnerType = 2 -- CHILD ACCOUNT
		AND p.PartnerGUID = @accountGUID
	), C AS (
		-- CONTACT TABLE
		SELECT
			con.JobTitle
		,	A.PKID
		FROM TR_CONTACT as con WITH (NOLOCK)
		INNER JOIN A
			ON A.PKID = con.ContactGUID
	), D AS (
		-- COMMUNICATION
		SELECT
			com.Phone 
		,	A.PKID
		FROM TR_COMMUNICATION as com WITH (NOLOCK)
		INNER JOIN A
			ON A.PKID = com.PartnerGUID
	), FINAL AS (
		SELECT 
			A.FirstName + ' ' +A.LastName	As [Name]
		,	C.JobTitle						AS [Job Title]
		,	D.Phone							AS [Office Phone]
		,	A.IsActive
		,	A.PKID
		FROM A
		INNER JOIN B
			ON B.PKID = A.PKID
		LEFT OUTER JOIN C
			ON C.PKID = A.PKID
		LEFT OUTER JOIN D
			ON D.PKID = A.PKID
	)


	SELECT [Name], [Job Title],[Office Phone], PKID
	FROM FINAL 
	ORDER BY [Name] ASC, IsActive DESC
END
GO


-- GetAccountContacts @USERID = NULL